from sqlalchemy.orm import scoped_session, sessionmaker
from pylons import config
import sqlalchemy as sa
from sqlalchemy import orm

from sqlalchemy import Column, MetaData, Table, types
from sqlalchemy.orm import relation
from sqlalchemy.schema import ForeignKey, UniqueConstraint

from datetime import datetime

# Global session manager.  Session() returns the session object
# appropriate for the current web request.
DBSession = scoped_session(sessionmaker(autoflush=True, autocommit=True))

# Session aware mapper:
mapper = DBSession.mapper

# Global metadata. If you have multiple databases with overlapping table
# names, you'll need a metadata for each database.
metadata = MetaData()

def init_model(engine):
    """Call me before using any of the tables or classes in the model."""
    
    # 'init_model' is called from environment.py automatically in TG2.
    # But if you are using the model with reflection elsewhere, you'll need to
    # call this before using the model.
    # If you are using reflection to introspect your database and create table objects
    # for you, your tables must be defined and mapped inside the init_model function, \
    # so that the engine is available. See the following example:

    # Reflected tables must be defined and mapped here.
    #    
    #global t_reflected
    #t_reflected = Table("Reflected", metadata,
    #    autoload=True, autoload_with=engine)
    #mapper(Reflected, t_reflected)

setting_table = Table("setting", metadata,
    Column('id', types.Integer, primary_key=True),
    Column('name', types.String(60), unique=True),
    Column('data', types.PickleType)
)
class Setting(object):
    
    @classmethod
    def by_name(cls, name):
        return cls.query.filter_by(name=name).first()
        
service_table = Table("service", metadata,
    Column('id', types.Integer, primary_key=True),
    Column('name', types.String(60), unique=True),
    Column('server', types.String(255)),
    Column('port', types.Integer, default=6667),
    Column('nickname', types.String(60)),
    Column('password', types.String(60), default=None),
    Column('username', types.String(60), default=None),
    Column('localaddress', types.String(15), default=""),
    Column('localport', types.Integer, default=0)
)
class Service(object):
    
    @classmethod
    def by_name(cls, name):
        if isinstance(name, basestring) and not isinstance(name, unicode):
            name = unicode(name, "utf8")
        return cls.query.filter_by(name=name).first()
        
    @property
    def enabled_channels(self):
        return Channel.query.filter_by(service_id=self.id, enabled=True).all()
        
    @property
    def disabled_channels(self):
        return Channel.query.filter_by(service_id=self.id, enabled=False).all()
        
    @property
    def connection_dict(self):
        return dict(server=self.server,
                    port=self.port,
                    nickname=self.nickname,
                    password=self.password,
                    username=self.username,
                    localaddress=self.localaddress,
                    localport=self.localport)


channel_table = Table("channel", metadata,
    Column('id', types.Integer, primary_key=True),
    Column('service_id', types.Integer, ForeignKey('service.id')),
    Column('name', types.Unicode(60)),
    Column('topic', types.UnicodeText, default=u""),
    Column('enabled', types.Boolean, default=True),
    UniqueConstraint('service_id', 'name', name='service_name_uix')
)
class Channel(object):
    #channel=channel, nick=source, line=message, time=datetime.now(), isaction=isaction
    
    def __init__(self, channel=None, nick=None, line=None, time=None, isaction=None):
        if channel:
            self.channel = channel
        if nick:
            self.nick = nick
        if line:
            self.line = line
        if time:
            self.time = time
        if isaction:
            self.isaction = isaction
    
    @classmethod
    def by_name_and_service(cls, chan, service):
        
        if isinstance(service, basestring):
            service = Service.by_name(service)
            q = Channel.query.filter(Channel.service_id==service.id)
            
        elif isinstance(service, int):
            q = Channel.query.filter(Channel.service_id==service)
            
        else:
            return None
        
        q = q.filter(Channel.name==chan)
        return q.first()
        
    def __str__(self):
        return self.name
        
    def last_messages(self, n=100):
        q = Message.query.filter(Message.channel_id==self.id)
        m_count = q.count()
        if m_count > n:
            q = q.limit(n).offset(m_count-n)
        
        return q.all()
        
    def day_changed(self):
        # TODO: remove this legacy code
        return False
        
    def messages_by_datetime(self, start, end):
        q = Message.query.filter(Message.channel_id==self.id)
        q = q.filter(Message.time >= start)
        q = q.filter(Message.time <= end)
        return q.all()
        
message_table = Table("message", metadata,
    Column('id', types.Integer, primary_key=True),
    Column('channel_id', types.Integer, ForeignKey('channel.id')),
    Column('time', types.DateTime, default=datetime.utcnow),
    Column('nick', types.Unicode(30)),
    Column('line', types.UnicodeText),
    # ALTER TABLE `message` ADD COLUMN `message_type` VARCHAR(50);
    Column('message_type', types.Unicode(50), default=u'message')
)
class Message(object):
    
    def __str__(self):
        return "%s: %s" % (self.nick, self.line)
        
    def json_data(self):
        return dict(id=self.id,
                    channel_id=self.channel_id,
                    time=str(self.time),
                    nick=self.nick,
                    line=self.line,
                    message_type=self.message_type)

# ===========
# = Mappers =
# ===========

mapper(Setting, setting_table)
mapper(Service, service_table)
mapper(Channel, channel_table, properties={
    'service': relation(Service, backref='channels')
})
mapper(Message, message_table, properties={
    'channel': relation(Channel, backref='messages')
})

from identity import User, Group, Permission 
from identity import users_table, groups_table, permissions_table, \
              user_group_table, group_permission_table